﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model.Cuotas;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using System.Data;

namespace DataBase.Cuotas
{
    public class BDCuotas
    {                           //ALVARO-PC
        const string conexion = @"Data Source=.\SQLEXPRESS;Initial Catalog=JCI;Integrated Security=True;Pooling=False";
        public static void EliminarPagoMensual(Int64 codi)
        {
            try
            {
                EliminarPago(codi);
                EliminarPagoCuota(codi);
            }
            catch (Exception)
            {
                throw new Exception("Error al eliminar");
            }
        }
        public static void EliminarPago(Int64 codi)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Delete From [Pago] Where CodigoPago=@cod";
                comando.Parameters.AddWithValue("cod", codi);
                
                canal.Open();
                comando.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw new Exception("Error al eliminar");
            }
        }
        public static void EliminarPagoCuota(Int64 codi)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Delete From [PagoCuota] Where CodigoPago=@cod";
                comando.Parameters.AddWithValue("cod", codi);

                canal.Open();
                comando.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw new Exception("Error al eliminar");
            }
        }
        public static Collection<Deudores> getdeudores()
        {
            string indiceMes = (DateTime.Now.Year).ToString() + (DateTime.Now.Month).ToString();

            Collection<Deudores> deumes= getDeudoresMes(Convert.ToInt32(indiceMes));
            Collection<DeudoresAnuales> deuAnua = getDeudoresAnio(DateTime.Now.Year);
            if (deumes!=null)
            {
                for (int i = 0; i < deumes.Count; i++)
                {
                    for (int j = 0; j < deuAnua.Count; j++)
                    {
                        if (deumes[i]._Carnet == deuAnua[j]._carnet)
                        {
                            int aux = DateTime.Now.Year - (deuAnua[j]._indice);
                            deumes[i]._Periodos_Anuales_Adeudados = aux;
                        }
                    }
                }    
            }
            return deumes;
        }
        public static Collection<Deudores> getDeudoresMes(int indip)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlDataAdapter comando = new SqlDataAdapter();
                comando.SelectCommand = new SqlCommand("Select Nombre,ApellidoPaterno,ApellidoMaterno,CI,Indice From [Miembro],[Pago],[PagoCuota] Where CI=CIPago And Pago.CodigoPago=PagoCuota.CodigoPago And (indice>=All ( SELECT Indice FROM [Pago],[PagoCuota] WHERE Pago.CIPago=CI AND Pago.CodigoPago=PagoCuota.CodigoPago ))", canal);
                comando.SelectCommand.Parameters.AddWithValue("pol", indip);
                canal.Open();
                DataTable dt = new DataTable();
                comando.Fill(dt);
                
                Collection<Deudores> IE = new Collection<Deudores>();
                
                foreach (DataRow row in dt.Rows)
                {
                    if (indip>Convert.ToInt32(row["Indice"]))
                    {
                        int anoIndip = indip / 100;
                        int mesIndip = indip - (anoIndip * 100);
                        int anoActua = (Convert.ToInt32(row["Indice"]))/100;
                        int mesActua = (Convert.ToInt32(row["Indice"])) - (anoActua * 100);
                        int meses;
                        
                        meses = (((anoIndip-anoActua)*12)+mesIndip)-mesActua;
                        
                        Deudores ingresoEgreso = new Deudores
                        {
                            _Nombre = (row["Nombre"]).ToString(),
                            _Apellido_Paterno = row["ApellidoPaterno"].ToString(),
                            _Apellido_Materno = (row["ApellidoMaterno"]).ToString(),
                            _Carnet = Convert.ToInt64(row["CI"]),
                            _Meses_Adeudados = meses
                        };
                        IE.Add(ingresoEgreso);    
                    }
                    
                }
                return IE;


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static Collection<DeudoresAnuales> getDeudoresAnio(int indip)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexion);
                SqlDataAdapter comando = new SqlDataAdapter();
                comando.SelectCommand = new SqlCommand("Select CIPago,Indice From [Miembro],[Pago],[PagoCuota] Where CI=CIPago And Pago.CodigoPago=PagoCuota.CodigoPago And Tipo=@doss And (indice>=All ( SELECT Indice FROM [Pago],[PagoCuota] WHERE Pago.CIPago=CI AND Pago.CodigoPago=PagoCuota.CodigoPago And Tipo=@doss ))", canal);
                comando.SelectCommand.Parameters.AddWithValue("doss", "dos");
                //comando.SelectCommand.Parameters.AddWithValue("tip", tipo);
                canal.Open();
                DataTable dt = new DataTable();
                comando.Fill(dt);

                Collection<DeudoresAnuales> IE = new Collection<DeudoresAnuales>();
                //int numero = 1;
                foreach (DataRow row in dt.Rows)
                {
                    if (indip > Convert.ToInt32(row["Indice"]))
                    {
                        DeudoresAnuales ingresoEgreso = new DeudoresAnuales
                        {
                            _carnet = Convert.ToInt64(row["CIPago"]),
                            _indice = Convert.ToInt32(row["Indice"])
                        };
                        IE.Add(ingresoEgreso);
                    }

                }
                return IE;


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void UpdateSiguientes(int aCodigo, int nuevoInd,int nueCanti)
        {
            try
            {
                
                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Update [PagoCuota] Set Indice=@nue,Cantidad=@nueCan Where CodigoPago=@ges";
                comando.Parameters.AddWithValue("nue", nuevoInd);
                comando.Parameters.AddWithValue("ges", aCodigo);
                comando.Parameters.AddWithValue("nueCan", nueCanti);
                canal.Open();
                comando.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static List<string> getCodigosParaCambiar(Int64 carnet, int disminuir, int indiceOrigen)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT Pago.CodigoPago,Indice,Cantidad FROM [Pago],[PagoCuota] WHERE Pago.CIPago=@auxx AND Pago.CodigoPago=PagoCuota.CodigoPago AND Tipo=@var12"; // AND Indice>=@pol
                cmd.Parameters.AddWithValue("@auxx", carnet);
                cmd.Parameters.AddWithValue("@var12", "uno");
                cmd.Parameters.AddWithValue("@pol", indiceOrigen);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                List<string> ressss = new List<string>();
                while (reader.Read())
                {
                    if ( (Convert.ToInt32(reader["Indice"].ToString())) >= indiceOrigen)
                    {
                        string assdf = reader["CodigoPago"].ToString();
                        assdf = assdf + " "+reader["Indice"].ToString();
                        assdf = assdf + " " + reader["Cantidad"].ToString();
                        ressss.Add(assdf);
                    }
                }

                reader.Close();

                return ressss;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error getting an area", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static List<PagoDeCuota> BuscarCuotasMesTodas( Int64 ci)
        {
            SqlConnection conn = null;
            try
            {
                //string[] resss = (fec.ToString()).Split('/', '-', ' ');
                //string vecha = resss[0] + "/" + resss[1] + "/" + resss[2];

                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT CIPago,Tipo,Indice,Cantidad,FechaDePago,Pago.CodigoPago FROM [Pago],[PagoCuota] WHERE Pago.CIPago=@auxx AND Pago.CodigoPago=PagoCuota.CodigoPago";
                cmd.Parameters.AddWithValue("@auxx", ci);
                
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                List<PagoDeCuota> lisnuevoo = new List<PagoDeCuota>();
                while (reader.Read())
                {
                    PagoDeCuota nuevoo = new PagoDeCuota();

                    nuevoo.carnet = Convert.ToInt64(reader["CIPago"]);
                    nuevoo.indice = Convert.ToInt32(reader["Indice"].ToString());
                    nuevoo.tipo = reader["Tipo"].ToString();
                    nuevoo.cantidad = Convert.ToInt32(reader["Cantidad"]);
                    nuevoo.fecha = reader["FechaDePago"].ToString();
                    nuevoo.codigo = Convert.ToInt64(reader["CodigoPago"]);
                    lisnuevoo.Add(nuevoo);


                }

                reader.Close();

                return lisnuevoo;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error getting an area", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static List<PagoDeCuota> BuscarCuotaMesMiembroPorFecha(DateTime fec,Int64 ci)
        {
            SqlConnection conn = null;
            try
            {
                string[] resss = (fec.ToString()).Split('/','-',' ') ;
                string vecha = resss[0] + "/" + resss[1] + "/" + resss[2];

                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT CIPago,Tipo,Indice,Cantidad FROM [Pago],[PagoCuota] WHERE Pago.CIPago=@auxx AND Pago.CodigoPago=PagoCuota.CodigoPago AND Tipo=@var12 AND FechaDePago=@va";
                cmd.Parameters.AddWithValue("@auxx", ci);
                cmd.Parameters.AddWithValue("@var12", "uno");
                cmd.Parameters.AddWithValue("@va", vecha);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                List< PagoDeCuota> lisnuevoo = new List<PagoDeCuota>();
                while (reader.Read())
                {
                        PagoDeCuota nuevoo = new PagoDeCuota();
                        
                        nuevoo.carnet = Convert.ToInt64(reader["CIPago"]);
                        nuevoo.indice = Convert.ToInt32(reader["Indice"].ToString());
                        nuevoo.tipo = reader["Tipo"].ToString();
                        nuevoo.cantidad = Convert.ToInt32(reader["Cantidad"]);

                        lisnuevoo.Add(nuevoo);
                    
                    
                }

                reader.Close();

                return lisnuevoo;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error getting an area", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static PagoDeCuota BuscarCuotaAnioMiembroPorFecha(string fech, Int64 ci)
        {
            return null;
        }

        public static PagoDeCuota BuscarCuotaMesMiembro(Int64 carnet)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT CIPago,Tipo,Indice FROM [Pago],[PagoCuota] WHERE Pago.CIPago=@auxx AND Pago.CodigoPago=PagoCuota.CodigoPago AND Tipo=@var12 AND Indice>=All ( SELECT Indice FROM [Pago],[PagoCuota] WHERE Pago.CIPago=@auxx AND Pago.CodigoPago=PagoCuota.CodigoPago)";
                cmd.Parameters.AddWithValue("@auxx", carnet);
                cmd.Parameters.AddWithValue("@var12", "uno");
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                PagoDeCuota nuevoo = new PagoDeCuota();
                if (reader.Read())
                {
                    nuevoo.carnet = Convert.ToInt64(reader["CIPago"]);
                    nuevoo.indice = Convert.ToInt32( reader["Indice"].ToString() ) ;
                    nuevoo.tipo = reader["Tipo"].ToString();
                    
                }
               
                reader.Close();

                return nuevoo;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error getting an area", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static PagoDeCuota BuscarCuotaAnioMiembro(Int64 carnet)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT CIPago,Tipo,Indice FROM [Pago],[PagoCuota] WHERE Pago.CIPago=@auxx AND Pago.CodigoPago=PagoCuota.CodigoPago AND Tipo=@var12 AND Indice>=All ( SELECT Indice FROM [Pago],[PagoCuota] WHERE Tipo=@var12 AND Pago.CodigoPago=PagoCuota.CodigoPago)";
                cmd.Parameters.AddWithValue("@auxx", carnet);
                cmd.Parameters.AddWithValue("@var12", "dos");
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                PagoDeCuota nuevoo = new PagoDeCuota();
                if (reader.Read())
                {
                    nuevoo.carnet = Convert.ToInt64(reader["CIPago"]);
                    nuevoo.indice = Convert.ToInt32(reader["Indice"].ToString());
                    nuevoo.tipo = reader["Tipo"].ToString();

                }

                reader.Close();

                return nuevoo;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error getting an area", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        //RealizarCobroMensual(carnett,"uno",nuevoIndice,Convert.ToInt32( tipomes) );
        public static bool RealizarCobroMensualPago(Int64 carnet, Int64 ultimo)
        {
            bool res = false;
            
            try
            {
                

                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Insert Into Pago(CodigoPago,CIPago,FechaDePago) Values (@ges,@est,@esd)";
                comando.Parameters.AddWithValue("ges", ultimo);
                comando.Parameters.AddWithValue("est", carnet);
                //------------
                DateTime aux = DateTime.Now;
                string[] lis = (aux.ToString("dd/MM/yyyy")).Split('/','-',' ');
                string resss = lis[0] + "/" + lis[1] + "/" + lis[2];
                //---------------
                comando.Parameters.AddWithValue("esd", resss);
                canal.Open();
                comando.ExecuteNonQuery();
                return true;
            }
            catch (SqlException)
            {
                return res;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        
            //return res;
        }
                                                           // "uno", nuevoIndice, Convert.ToInt32(tipomes), ultimo + 1) 
        public static bool RealizarCobroMensualPagoCuota( string tipo,int nuevoIndice,int cant,Int64 nuecodigo)
        {
            bool res = false;
            
            try
            {
                

                SqlConnection canal = new SqlConnection(conexion);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Insert Into PagoCuota(CodigoPago,Tipo,Indice,Cantidad) Values (@uno,@dos,@tre,@cua)";
                comando.Parameters.AddWithValue("uno", nuecodigo);
                comando.Parameters.AddWithValue("dos", tipo);
                comando.Parameters.AddWithValue("tre", nuevoIndice);
                comando.Parameters.AddWithValue("cua", cant);
                canal.Open();
                comando.ExecuteNonQuery();
                return true;
            }
            catch (SqlException)
            {
                return res;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static Int64 ultimaCodigo()
        {
            Int64 res = 1;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT CodigoPago FROM [Pago] WHERE CodigoPago>=All ( SELECT CodigoPago FROM [Pago] )";
                
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    res = 1; ;
                }
                if (reader.Read())
                {
                    res = Convert.ToInt64( reader["CodigoPago"]);
                }

                reader.Close();

                return res;
            }
            catch (SqlException ex)
            {
                throw  ex;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }


    }
}
